Attribute VB_Name = "modODBCMitel"

'---------------------------------------------------------------------------------------
'Module     : modODBC
'Author     : Chris Elwell
'Purpose    : You must write the code required to get/writeback data from/to your customer's data source.
'
'NEW        This example uses the AddrVerify.mdb. A DSN is setup as AddrVerify.
'           Regardless of the integration method, CICs must have a module that contains
'           specific sub routines. All of these subs/functions listed below must exist
'           as they are called from the main program.
'
'           Public Function GetData(ByVal strKey As String) As Boolean - this routine is
'               called when the Order ID field loses focus. This is where you would code to
'               get order data from a customer and populate the screen fields. You can create
'               and call as many support routines as necessary. Also, this routine should
'               return a boolean True if getting the order data was succesful and a boolean
'               False if it was not.
'NEW        Public Function GetPickupData(ByVal strKey As String) As Boolean - this routine is
'               called when the Order ID on the FedEx Returns for field loses focus.
'               This operates the same as GetData above but for use obtaining the Pickup From
'               address info on the Call Tag/Express Tag form.
'           Public Sub WriteReply() - this is called after a successful shipment. A typical
'               use would be to write FXRS reply data back to a customer's system, but can be
'               used for anything that you want to have happen after a package is shipped.
'           Public Sub WriteReturnReply() - this is called after a successful Return Label
'               shipment. Use this to write FXRS reply data back to the customer's system.
'               This is called on both regular and online return labels.
'NEW        Public Sub WriteBOLReply() - this is called after a successful FedEx Freight BOL shipment.
'NEW        Public Sub WriteBatchEditReply() - this is called after a successful Batch Error
'           Edit shipment.
'NEW        Public Sub WriteTagReply() - this is called after a successful Call/Express Tag
'NEW        Public Sub WriteCancelTagReply() - this is called after a Call/Express Tag is Canceled
'           Public Sub WriteError() - this is called after an error is returned from FXRS.
'           Public Sub WriteDelete(ByVal strTracking As String) - this is called after a
'               package is successfully deleted.
'           Public Sub WriteReturnDelete(ByVal strTracking As String) - this is called after
'               a successful Return Label delete. Use this to write FXRS reply data back to
'               the customer's system. This is called on both regular and online return labels.
'           Public Sub AfterClose() - this is called after FXRS is closed.
'NEW        Public Sub AfterLocationChange() - this is called after the Current Location is changed
'               on frmShip.
'UPDATED    Public Sub BeforeClose() - this is called just before an FXRS close transaction
'               is sent and must return a boolean True for the Close to proceed.
'           Public Function BeforeShip() - Return TRUE to continue shipment, False to cancel.
'           Public Sub Initialize() - this is called once on program execution.
'           Public Sub BeforeBatch() - this is called before processing a batch file.
'           Public Sub BeforeBatchShip() - this is called before each individual batch
'                                          transaction.
'           Public Sub AfterBatchShip() - this is called after each shipment in batch processing.
'           Public Sub AfterBatch() - this is called after a completed batch shipment.
'           Public Sub AfterAddrLookup() - this is called after cboCustID, cboContact, and
'                                          cboCompany lose focus
'           Public Sub AfterDeptLostFocus() - this is called after cboDept or f38 loses focus.
'           Public Sub AfterAddrVerifyUpdate() - this is called after the Update button is
'                                            clicked on the Verify Address form.
'New        Public Sub AfterAddrVerify(ByVal intStatusCode As Integer) - this is called after
'               Address Verification. The status code indicates 1 address verified, 2 there was
'               a soft error returned, 3 there was a hard error returned.
'---------------------------------------------------------------------------------------

' See Project properties for Conditional Compilation
#If Mitel Then

Option Explicit

Public g_strSelectService As String
Public g_strChargeFrt As String
Public g_strImportYes As String                     'Establishes whether or not the order is pulled by ODBC
Public g_strRef As String                           'holds 1st reference
Public g_strLTLRef As String
Public g_strLTLProNum As String
Public g_strLTLPkgCt As String
Public g_strLTLWeight As String
Public g_strTotalWeight As String
Public g_strMPSWgt As String
Public g_intMPSWgt As Integer
Public g_intTotMPSWgt As Integer

Public g_strFreightCount As String
Public g_strFreightWeight As String
Public g_lngTotalWeight As Long
Public g_strReturnLbl As String                     'defines a return label
Public g_strKey As String                           'holds order number

Public g_strOrder As String                         'another holder for order number



    
'--------------------------------------------------
'Declare module level variables that will determine
'if the shipment is "MANUAL" or "AUTOMATIC"
'--------------------------------------------------
Private m_strShipType           As String

'------------------------------------
'Declare the RecordSet and Connection
'------------------------------------
Private m_rsRecords             As ADODB.Recordset
Private m_rsConnection          As ADODB.Connection
Private m_rsConnection_SAP      As ADODB.Connection

'-----------------------------------------
'Declare variables for customer's ODBC DSN (ORACLE)
'-----------------------------------------
Private Const m_strCUSTOMER_DSN = "PROD"
Private Const m_strDSN_USERNAME = "shipping"
Private Const m_strDSN_PASSWORD = "send2u"

'-----------------------------------------
'Declare variables for customer's ODBC DSN (SAP)
'-----------------------------------------
Private Const m_strCUSTOMER_DSN_SAP = "SAP"
Private Const m_strDSN_USERNAME_SAP = "fedex"
Private Const m_strDSN_PASSWORD_SAP = "fedexview"

Private strTableImport As String    'string value for the table name on import
Private strTableExport As String    'string value for the table name on export
Private intDatabase As Integer      'represents databases to pull data from (8=SAP,all else = Oracle))
Public strDatabase As String        'represents databsees in text value ("SAP","PROD")

'---------------------------------------------------------------------------------------
' Procedure : GetData()
' DateTime  : 7/5/2004
' Purpose   : REQUIRED SUB. Called from frmShip.f1_LostFocus, which is the OrderID field.
'             Call routines needed to get data from the customer's system and assign the
'             value to the appropriate fields on the Ship form. This example uses ODBC.
'---------------------------------------------------------------------------------------
'
Public Function GetPickupData(ByVal strKey As String) As Boolean
    
    GetPickupData = True
    
    
End Function
'---------------------------------------------------------------------------------------
' Procedure : GetPickupData()
' DateTime  : 1/30/2006
' Purpose   : REQUIRED SUB. Called from frmAddTags.f1_LostFocus, which is the OrderID field.
'             Call routines needed to get data from the customer's system and assign the
'             value to the appropriate fields on the Ship form. This example uses ODBC.
'---------------------------------------------------------------------------------------
'
Public Function GetData(ByVal strKey As String) As Boolean
    
    On Error GoTo ErrHandler

    Dim strSQL As String        'SQL statement
    
    '--------------
    'Reference Data
    '--------------
    Dim strOrderNo   As String   'Location ID - Department Notes
    Dim strPONo      As String   'Invoice Number - Invoice
    Dim strInvoiceNo As String
    '-----------
    'ShipTo Data
    '-----------
    Dim strCompany      As String   'Company
    Dim strContact      As String   'Contact
    Dim strAddress1     As String   'Address Line 1
    Dim strAddress2     As String   'Address Line 2
    Dim strCity         As String   'City
    Dim strState        As String   'State
    Dim strZip          As String   'Zip
    Dim strPhone        As String   'Phone
    Dim strCountry      As String   'Country
    Dim strShipVia      As String   'Service Type
    Dim strWeight       As String   'Package Weight
    
            
    '-------------------------------
    'determing which database to use
    '-------------------------------
    intDatabase = Left(CDbl(strKey), 1)
    If intDatabase = 8 Then
        If Len(Trim(CDbl(strKey))) = 8 Then
            'capture order number
            strKey = "00" & Trim(CDbl(strKey))
            g_strKey = strKey
            frmShip.f1 = strKey
            strDatabase = "SAP"
            strTableImport = "FEDEX.ZDELIVERY_ADDR_VIEW"
            strTableExport = "FEDEX.ZDELIVERY_DATA_VIEW"
        Else
            'capture order number
            g_strKey = strKey
            strDatabase = "PROD"
            strTableImport = "APPS.INTL_SHIPPING_FEDEX_VIEW"
            strTableExport = "ITL_FEDEX_DELIVERIES"
        End If
    Else
        'capture order number
        g_strKey = strKey
        strDatabase = "PROD"
        strTableImport = "APPS.INTL_SHIPPING_FEDEX_VIEW"
        strTableExport = "ITL_FEDEX_DELIVERIES"
    End If
    
    '----------------------
    'create sql statement
    '----------------------
    strSQL = "Select CUSTOMER, ORDER_NUMBER, PO_NUMBER, ADDRESS1, ADDRESS2, CITY, STATE," _
            & " ZIP, CONTACT, PHONE FROM " & strTableImport & " WHERE PICK_SLIP_NUMBER = '" _
            & g_strKey & "'"
    '---------------------------------
    'Verify the connection to the DSN
    '--------------------------------
    If Not Connect(strDatabase) Then Exit Function
    
    '-------------------------
    'Instantiate the Recordset
    '-------------------------
    m_rsRecords.CursorLocation = adUseClient
    
    '-----------------------------------------
    'Execute the SQL statement against the DSN
    '-----------------------------------------
    Select Case strDatabase
        Case "PROD"
            m_rsRecords.Open strSQL, m_rsConnection, adOpenStatic
        Case "SAP"
            m_rsRecords.Open strSQL, m_rsConnection_SAP, adOpenStatic
    End Select
    
    '-------------------------------
    'Close the connection to the DSN
    '-------------------------------
    Set m_rsRecords.ActiveConnection = Nothing
    
    '------------------------------------
    'No records were found, set to Manual, return False, Goto Release
    '------------------------------------
    If m_rsRecords.BOF And m_rsRecords.EOF Then
        
        '------------------------------------------
        'Set the m_strShipType variable to "MANUAL"
        '------------------------------------------
        m_strShipType = "MANUAL"
        
        GetData = False
        
        GoTo Release
    
    End If
    
    m_strShipType = "AUTOMATIC"
    
    '----------------------------------
    'Enable the PO Number field (f3056)
    '----------------------------------
    frmShip.f3056.Enabled = True
    
    '---------------------------------------------------------------
    'Set the returned package data equal to its appropriate variable
    '---------------------------------------------------------------
    'Reference Information
    '---------------------
    strPONo = Trim(UCase(m_rsRecords!PO_NUMBER & ""))       'Location ID - Department Notes (f38)
    strOrderNo = Trim(UCase(m_rsRecords!ORDER_NUMBER & ""))     'Invoice Number - Invoice (f3057)
    '---------------------
    'Recipient Information
    '---------------------
    
    strCompany = Trim(UCase(m_rsRecords!CUSTOMER & ""))               'Company (f11)
    strContact = Trim(UCase(m_rsRecords!Contact & ""))               'Company (f11)
    strAddress1 = Trim(UCase(m_rsRecords!ADDRESS1 & ""))           'Address Line 1 (f13)
    strAddress2 = Trim(UCase(m_rsRecords!ADDRESS2 & ""))           'Address Line 2 (f14)
    strCity = Trim(UCase(m_rsRecords!City & ""))                    'City (f15)
    strState = Trim(UCase(m_rsRecords!State & ""))                  'State (f16)
    strZip = Trim(UCase(m_rsRecords!Zip & ""))              'Zip (f17)
    strCountry = ""
    strPhone = Trim(UCase(m_rsRecords!Phone & ""))
    If Len(strPhone) < 10 Then
    'If IsNull(strPhone) Or strPhone = "NULL" Or strPhone = "" Then
        strPhone = "0000000000"
    End If
    
    '----------------------
    'Check the Country Code
    '----------------------
    Select Case strCountry
        Case "", "USA"
            strCountry = "US"
        Case "CANADA"
            strCountry = "CA"
        Case Else
            strCountry = "US"
    End Select
    
    '--------------------------------
    'Assign the collected data to FSMS Client screen
    '--------------------------------
    With frmShip
        'Recipient Data
        .f50.Text = (strCountry)   'Country Code
        .f11.Text = (strCompany)   'Company Name
        .f12.Text = (strContact)      'Contact Name
        .f13.Text = (strAddress1)  'Address Line 1
        .f14.Text = (strAddress2)  'Address Line 2
        .f15.Text = (strCity)      'City
        .f16.Text = (strState)     'State
        .f17.Text = (strZip)       'Postal Code
        .f18.Text = strPhone                            'Phone
        
        '---------------------
        'Reference Information
        '---------------------
        .f3056.Text = RemoveSpecial(strOrderNo)   'Invoice (f3057)
        .f25.Text = "PO: " & RemoveSpecial(strPONo)
        'when processing SAP orders
        'use multiple 3rd party acct
        'base on domestic or international
        If strDatabase = "SAP" Then
            .f23 = "3"
            If strCountry <> "US" Then
                .f20 = "331885304"
            Else
                .f20 = "175863524"
            End If
        Else
            .f23.Text = "1"
        End If
        .f1670.Text = "1"

'        If g_strSelectService = "" Then
'            frmService.Show
'        End If
'        .f1274.Text = g_strSelectService
        
        .f1274 = "01"
        .f1273 = "01"
        g_strChargeFrt = "N"
        g_strImportYes = "Y"
        
        Call SetMessage("Dest: " & .f15.Text & ", " & Left(.f16.Text, 2) & ", " & .f17.Text & "    Charge Freight =" & g_strChargeFrt, 12, vbBlack, True)

    End With
    
    With frmBOL
        .f1.Text = strOrderNo
        .f6107.Text = "1"
        .cboItemID.Text = "TelephoneEquip"
        .f6105.Text = "92.5"
        .f6106.Text = "63300-07"
        .f3056.Text = strPONo
        .f4683.Text = strOrderNo
        .f79.Text = "Telephone Equipment"
    End With
    
    GetData = True
    If frmShip.f116.Enabled Then frmShip.f116.SetFocus

Release:
    Call Kill(strDatabase)

    Exit Function

ErrHandler:
    MsgBox Err.Description & " in modODBC.GetData", vbCritical Or _
                            vbSystemModal, "Order Lookup Error"
    Resume Release

End Function
'---------------------------------------------------------------------------------------
' Procedure : WriteReply()
' DateTime  : 2/14/2005
' Purpose   : REQUIRED SUB. Called after a successful ship transaction.
'             This example shows how to obtain multiple occurrence fields from the
'             FXRS reply and how to obtain multiple occurrence fields included in the
'             input transaction but not returned in the FXRS reply.
'---------------------------------------------------------------------------------------
'
Public Sub WriteReply()
On Local Error GoTo Handler
    
    Dim i As Integer
    Dim j As Integer
    
    Dim f1 As String                    'OrderID (in TransID field)
    Dim f35 As String                   'Total surcharges
    Dim f37 As String                   'Gross charge
    Dim f60 As String                   'Billed weight
    Dim f116 As String                  'Num pkgs
    Dim f25 As String                   'Reference field
    Dim strInsert As String             'SQL INSERT
    Dim strMPS As String                'MPS SELECT query
    Dim rsMPS As New ADODB.Recordset    'Used to capture MPS input fields
    Dim lng37 As Double

    'Declare string arrays for trk#s and declared values
    Dim str29() As String
    Dim str26() As String

    Dim intNumPkgs As Integer

    'Get # of pkgs
    intNumPkgs = Val(frmShip.f116)
    

    'ReDim the string array using the # of pkgs
    ReDim str29(intNumPkgs)
    ReDim str26(intNumPkgs)
        
    If g_strImportYes <> "" Then
        If Not Connect(strDatabase) Then Exit Sub
        
        g_strRef = frmShip.f25
    
        'Added 4/14/05 for SmartPost - see Else statement below
        If g_str1274 <> "99" Then
            'Get the fields you wish from the reply transaction
            With frmShip.g_clsParser
                For i = 0 To .TotalFields - 1
                    Select Case .Field(i)
                        Case "1"
                            f1 = .Data(i)           'TransID
                        Case "29"
                            str29(1) = .Data(i)     'First trk#
                        Case "35"
                            f35 = .Data(i)          'Total Surcharges
                        Case "37"
                            f37 = .Data(i)          'Net Charges
                        Case "60"
                            f60 = .Data(i)          'Billed Weight
                        Case "116"
                            f116 = .Data(i)         '# Pkgs
                    End Select
    
                    'Get multiple occurrence tracking #s
                    'Note: This will capture Int'l MPS child trk#s as well as domestic MPS
                    If intNumPkgs > 1 And frmShip.chkDuplicate = 0 Then
                        For j = 2 To intNumPkgs
                            If .Field(i) = "29-" & Trim(Str(j)) Then str29(j) = .Data(i)
                        Next
                    End If
                Next
            End With
    
            'Query to get fields saved in MPS table for MPS transactions
            strMPS = "SELECT * FROM MPS WHERE f1 = '" & f1 & "'"
            'Open recordset
            rsMPS.Open strMPS, g_connFSMSClient, adOpenStatic, adLockBatchOptimistic
    
        'Added 4/14/05 for SmartPost
        Else
            str29(1) = Right(g_strDCB, 20)
            f1 = g_str1
            f35 = g_str37
            f37 = g_str37
            str26(1) = "0"
            f60 = g_str60
            f116 = "1"
        End If
        
        lng37 = Val(g_str37) * 0.01
        If g_str1274 <> "06" Then   'makes the exception for First Overnight Svc
            lng37 = lng37 * 1.6
        End If
            
        'Perform Insert query for each package
        For i = 1 To intNumPkgs
        Select Case strDatabase
                    Case "PROD"
                        If str29(i) <> "" Then
                            strInsert = "INSERT INTO " & strTableExport & " "
                            strInsert = strInsert & "VALUES("
                            strInsert = strInsert & SingleQuotes(g_str1) & ","
                            strInsert = strInsert & SingleQuotes(g_str1274) & ","
                            strInsert = strInsert & SingleQuotes(lng37) & ","
                            strInsert = strInsert & SingleQuotes(g_strChargeFrt) & ","
                            strInsert = strInsert & SingleQuotes(intNumPkgs) & ","
                            strInsert = strInsert & SingleQuotes(str29(i)) & ","
                            strInsert = strInsert & SingleQuotes(g_strRef) & ","
                            strInsert = strInsert & "'',"
                            'Windows user
                            strInsert = strInsert & SingleQuotes(GetUserID())
                            strInsert = strInsert & ",'','',"
                            strInsert = strInsert & SingleQuotes(CInt(0 & f60)) & ","
                            strInsert = strInsert & "'N'"
                            strInsert = strInsert & ")"
                            'debugprintstrInsert
                            m_rsConnection.Execute strInsert
                        End If
                    Case "SAP"
                        If str29(i) <> "" Then
                            strInsert = "INSERT INTO " & strTableExport & " "
                            strInsert = strInsert & "(DELIVERY_NAME, SHIP_METHOD_CODE,"
                            strInsert = strInsert & " FREIGHT_CHARGE, CHARGE_FREIGHT_F, CARTON_COUNT, WAYBILL, SHIP_REFERENCE,"
                            strInsert = strInsert & " SHIP_DATE, CREATED_BY, PROCESSED_FLAG, ERROR_TEXT, WEIGHT, ZPROGRAM, PROCESSED)"
                            strInsert = strInsert & " VALUES("
                            strInsert = strInsert & SingleQuotes(g_str1) & ","
                            strInsert = strInsert & SingleQuotes(g_str1274) & ","
                            strInsert = strInsert & SingleQuotes(lng37) & ","
                            strInsert = strInsert & SingleQuotes(g_strChargeFrt) & ","
                            strInsert = strInsert & SingleQuotes(intNumPkgs) & ","
                            strInsert = strInsert & SingleQuotes(str29(i)) & ","
                            strInsert = strInsert & SingleQuotes(g_strRef) & ","
                            strInsert = strInsert & Format(Date, "yyyymmdd") & ","
                            'Windows user
                            strInsert = strInsert & SingleQuotes(GetUserID())
                            strInsert = strInsert & ",'0','0',"
                            strInsert = strInsert & SingleQuotes(CInt(0 & f60)) & ","
                            strInsert = strInsert & "'N'"
                            strInsert = strInsert & ",'0')"
                            'debugprintstrInsert
                            'MsgBox strInsert
                            m_rsConnection_SAP.Execute strInsert
                        End If
                End Select
        Next
        
        g_strImportYes = ""
    
    
Release:
        Call Kill(strDatabase)
        If rsMPS.State = adStateOpen Then rsMPS.Close
        Set rsMPS = Nothing
    
    End If 'If g_strImportYes <> ""
        
    Exit Sub

    

Handler:
    MsgBox Err.Description, , "modODBC.WriteReply"
    Resume Release

    
    
End Sub
'---------------------------------------------------------------------------------------
' Procedure : WriteReturnReply()
' DateTime  : 7/14/2005
' Purpose   : REQUIRED SUB. Called after a successful return label transaction.
'             This is called for both regular and online return labels.
'---------------------------------------------------------------------------------------
'
Public Sub WriteReturnReply()
On Local Error GoTo Handler
    
    Dim i As Integer
    Dim j As Integer
    
    Dim f1 As String                    'OrderID (in TransID field)
    Dim f35 As String                   'Total surcharges
    Dim f37 As String                   'Net charge
    Dim f60 As String                   'Billed weight
    Dim f116 As String                  'Num pkgs
    Dim strInsert As String             'SQL INSERT
    Dim strMPS As String                'MPS SELECT query
    Dim rsMPS As New ADODB.Recordset    'Used to capture MPS input fields
    
    
    If Not Connect(strDatabase) Then Exit Sub

    'Declare string arrays for trk#s and declared values
    Dim str29() As String
    Dim str26() As String
    
   ' If modMain.g_strImportYes <> "" Then    'And g_strIsProd <> "Y"
    'If Not Connect Then Exit Sub
    

    Dim intNumPkgs As Integer

    'Get # of pkgs
    intNumPkgs = Val(frmShip.f116)

    'ReDim the string array using the # of pkgs
    ReDim str29(intNumPkgs)
    ReDim str26(intNumPkgs)

    'Added 4/14/05 for SmartPost - see Else statement below
    If g_str1274 <> "99" Then
        'Get the fields you wish from the reply transaction
        With frmShip.g_clsParser
            For i = 0 To .TotalFields - 1
                Select Case .Field(i)
                    Case "1"
                        f1 = .Data(1)           'TransID
                    Case "29"
                        str29(1) = .Data(i)     'First trk#
                    Case "35"
                        f35 = .Data(i)          'Total Surcharges
                    Case "37"
                        f37 = .Data(i)          'Net Charges
                    Case "60"
                        f60 = .Data(i)          'Billed Weight
                    Case "116"
                        f116 = .Data(i)         '# Pkgs
                End Select

                'Get multiple occurrence tracking #s
                'Note: This will capture Int'l MPS child trk#s as well as domestic MPS
                If intNumPkgs > 1 And frmShip.chkDuplicate = 0 Then
                    For j = 2 To intNumPkgs
                        If .Field(i) = "29-" & Trim(Str(j)) Then str29(j) = .Data(i)
                    Next
                End If
            Next
        End With

        'Query to get fields saved in MPS table for MPS transactions
        strMPS = "SELECT * FROM MPS WHERE f1 = '" & f1 & "'"
        'Open recordset
        rsMPS.Open strMPS, g_connFSMSClient, adOpenStatic, adLockBatchOptimistic

        'Loop through the MPS recordset and assign the Declared Value fields
        'Note: You can also capture any other MPS field using this loop by declaring
        'variables for those data element assigning the values here.
        i = 1
        If Not rsMPS.EOF Then
            Do Until rsMPS.EOF
                str26(i) = rsMPS!g_str26 & ""
                rsMPS.MoveNext
                i = i + 1
            Loop
        Else
            str26(1) = g_str26
        End If
    'Added 4/14/05 for SmartPost
    Else
        str29(1) = Right(g_strDCB, 20)
        f1 = g_str1
        f35 = g_str37
        f37 = g_str37
'        Dim lng37 As Double
 '       lng37 = Val(g_str37)
'        lng37 = lng37 * 0.01
        
        
        str26(1) = "0"
        f60 = g_str60
        f116 = "1"
    End If
        Dim lng37 As Double
                lng37 = Val(g_str37)
                lng37 = lng37 * 0.01
                'lng37 = lng37 * 1.5
        
    'Perform Insert query for each package
    For i = 1 To intNumPkgs
        Select Case strDatabase
                    Case "PROD"
                        If str29(i) <> "" Then
                            strInsert = "INSERT INTO " & strTableExport & " "
                            strInsert = strInsert & "VALUES("
                            strInsert = strInsert & SingleQuotes(g_strOrder) & ","
                            strInsert = strInsert & SingleQuotes("92") & ","
                            strInsert = strInsert & SingleQuotes(lng37) & ","
                            strInsert = strInsert & SingleQuotes(g_strChargeFrt) & ","
                            strInsert = strInsert & SingleQuotes(intNumPkgs) & ","
                            strInsert = strInsert & SingleQuotes(str29(i)) & ","
                            strInsert = strInsert & SingleQuotes(g_strRef) & ","
                            strInsert = strInsert & "'',"
                            'Windows user
                            strInsert = strInsert & SingleQuotes(GetUserID())
                            strInsert = strInsert & ",'','',"
                            strInsert = strInsert & SingleQuotes(CInt(0 & f60)) & ","
                            strInsert = strInsert & "'N'"
                            strInsert = strInsert & ")"
                            'debugprintstrInsert
                            'MsgBox strInsert
                            m_rsConnection.Execute strInsert
                        End If
                    Case "SAP"
'                        If str29(i) <> "" Then
'                            strInsert = "INSERT INTO " & strTableExport & " "
'                            strInsert = strInsert & "(DELIVERY_NAME, SHIP_METHOD_CODE,"
'                            strInsert = strInsert & " FREIGHT_CHARGE, CHARGE_FREIGHT_F, CARTON_COUNT, WAYBILL, SHIP_REFERENCE,"
'                            strInsert = strInsert & " SHIP_DATE, CREATED_BY, PROCESSED_FLAG, ERROR_TEXT, WEIGHT, ZPROGRAM, PROCESSED)"
'                            strInsert = strInsert & " VALUES("
'                            strInsert = strInsert & SingleQuotes(g_strOrder) & ","
'                            strInsert = strInsert & SingleQuotes(g_str1274) & ","
'                            strInsert = strInsert & SingleQuotes(lng37) & ","
'                            strInsert = strInsert & SingleQuotes(g_strChargeFrt) & ","
'                            strInsert = strInsert & SingleQuotes(intNumPkgs) & ","
'                            strInsert = strInsert & SingleQuotes(str29(i)) & ","
'                            strInsert = strInsert & SingleQuotes(g_strRef) & ","
'                            strInsert = strInsert & Format(Date, "yyyymmdd") & ","
'                            'Windows user
'                            strInsert = strInsert & SingleQuotes(GetUserID())
'                            strInsert = strInsert & ",'0','0',"
'                            strInsert = strInsert & SingleQuotes(CInt(0 & f60)) & ","
'                            strInsert = strInsert & "'N'"
'                            strInsert = strInsert & ",'0')"
'                            'debugprintstrInsert
'                            'MsgBox strInsert
'                            m_rsConnection_SAP.Execute strInsert
'                        End If
                End Select

            
'            strInsert = strInsert & "'',"
'            ' windows user
'            'strInsert = strInsert & "'Cafeuser'"
'            strInsert = strInsert & SingleQuotes(GetUserID())
'            strInsert = strInsert & ",'','',"
'            strInsert = strInsert & SingleQuotes(g_strTotalWeight) & ","
'            strInsert = strInsert & "'N')"
            
           ' MsgBox (strInsert)
            'strInsert = strInsert & SingleQuotes(FormatCharges(f35)) & ","
            'strInsert = strInsert & SingleQuotes(FormatCharges(f37)) & ","
            'strInsert = strInsert & SingleQuotes(str26(i)) & ","
            'strInsert = strInsert & SingleQuotes(f60) & ","
            'strInsert = strInsert & SingleQuotes(f116) & ","
            'strInsert = strInsert & "'Shipped')"
          ' MsgBox (strInsert)
            'm_rsConnection.Execute strInsert
    Next

Release:
    Call Kill(strDatabase)
    If rsMPS.State = adStateOpen Then rsMPS.Close
    Set rsMPS = Nothing
    Exit Sub

Handler:
    MsgBox Err.Description, , "modODBC.WriteReply"
    Resume Release
    
' End If
End Sub

'---------------------------------------------------------------------------------------
' Procedure : WriteBOLReply()
' DateTime  : 1/11/2006
' Purpose   : REQUIRED SUB. Called after a successful FedEx Freight BOL transaction.
'             This is called for both regular and online return labels.
'---------------------------------------------------------------------------------------
'
Public Sub WriteBOLReply()
On Local Error GoTo Handler
    
    Dim i As Integer
    Dim j As Integer
    
    Dim f1 As String                    'OrderID (in TransID field)
    Dim f35 As String                   'Total surcharges
    Dim f37 As String                   'Net charge
    Dim f60 As String                   'Billed weight
    Dim f116 As String                  'Num pkgs
    Dim strInsert As String             'SQL INSERT
    Dim strMPS As String                'MPS SELECT query
    Dim rsMPS As New ADODB.Recordset    'Used to capture MPS input fields
    Dim strKey As String
    
    'If Not Connect(strDatabase) Then Exit Sub

    'Declare string arrays for trk#s and declared values
    Dim str29() As String
    Dim str26() As String
    
    If g_strImportYes <> "" Then    'And g_strIsProd <> "Y"
    If Not Connect(strDatabase) Then Exit Sub
    

    Dim intNumPkgs As Integer

    'Get # of pkgs
    intNumPkgs = Val(frmShip.f116)

    'ReDim the string array using the # of pkgs
    ReDim str29(intNumPkgs)
    ReDim str26(intNumPkgs)
    
    strKey = frmShip.f1
    g_strRef = frmShip.f25


    'Added 4/14/05 for SmartPost - see Else statement below
    If g_str1274 <> "99" Then
        'Get the fields you wish from the reply transaction
        With frmShip.g_clsParser
            For i = 0 To .TotalFields - 1
                Select Case .Field(i)
                    Case "1"
                        f1 = .Data(i)           'TransID
                    Case "29"
                        str29(1) = .Data(i)     'First trk#
                    Case "35"
                        f35 = .Data(i)          'Total Surcharges
                    Case "37"
                        f37 = .Data(i)          'Net Charges
                    Case "60"
                        f60 = .Data(i)          'Billed Weight
                    Case "116"
                        f116 = .Data(i)         '# Pkgs
                End Select

                'Get multiple occurrence tracking #s
                'Note: This will capture Int'l MPS child trk#s as well as domestic MPS
                If intNumPkgs > 1 And frmShip.chkDuplicate = 0 Then
                    For j = 2 To intNumPkgs
                        If .Field(i) = "29-" & Trim(Str(j)) Then str29(j) = .Data(i)
                    Next
                End If
            Next
        End With

        'Query to get fields saved in MPS table for MPS transactions
        strMPS = "SELECT * FROM MPS WHERE f1 = '" & f1 & "'"
        'Open recordset
        rsMPS.Open strMPS, g_connFSMSClient, adOpenStatic, adLockBatchOptimistic

        'Loop through the MPS recordset and assign the Declared Value fields
        'Note: You can also capture any other MPS field using this loop by declaring
        'variables for those data element assigning the values here.
        i = 1
        If Not rsMPS.EOF Then
            Do Until rsMPS.EOF
                str26(i) = rsMPS!g_str26 & ""
                rsMPS.MoveNext
                i = i + 1
            Loop
        Else
            str26(1) = g_str26
        End If
    'Added 4/14/05 for SmartPost
    Else
        str29(1) = Right(g_strDCB, 20)
        f1 = g_str1
        f35 = g_str37
        f37 = g_str37
'        Dim lng37 As Double
 '       lng37 = Val(g_str37)
'        lng37 = lng37 * 0.01
        
        
        str26(1) = "0"
        f60 = g_str60
        f116 = "1"
    End If
        Dim lng37 As Double
                lng37 = Val(g_str37)
                lng37 = lng37 * 0.01
            
                'lng37 = lng37 * 1.5
        
    'Perform Insert query for each package
   
        If str29(i) <> "" Then
           
                Select Case strDatabase
                    Case "PROD"
                        strInsert = "INSERT INTO " & strTableExport & " "
                        strInsert = strInsert & "VALUES("
                        strInsert = strInsert & SingleQuotes(strKey) & ","
                        strInsert = strInsert & SingleQuotes(g_str1274) & ","
                        strInsert = strInsert & SingleQuotes(lng37) & ","
                        strInsert = strInsert & SingleQuotes(g_strChargeFrt) & ","
                        strInsert = strInsert & SingleQuotes(g_strFreightCount) & ","
                        strInsert = strInsert & SingleQuotes(frmBOL.f1222.Text) & ","
                        strInsert = strInsert & SingleQuotes(g_strRef) & ","
                        strInsert = strInsert & "'',"
                        'Windows user
                        strInsert = strInsert & SingleQuotes(GetUserID())
                        strInsert = strInsert & ",'','',"
                        strInsert = strInsert & SingleQuotes(g_strTotalWeight) & ","
                        strInsert = strInsert & "'N'"
                        strInsert = strInsert & ")"
                        For i = 1 To g_strFreightCount
                            m_rsConnection.Execute strInsert
                        Next
                    Case "SAP"
                        strInsert = "INSERT INTO " & strTableExport & " "
                        strInsert = strInsert & "(DELIVERY_NAME, SHIP_METHOD_CODE,"
                        strInsert = strInsert & " FREIGHT_CHARGE, CHARGE_FREIGHT_F, CARTON_COUNT, WAYBILL, SHIP_REFERENCE,"
                        strInsert = strInsert & " SHIP_DATE, CREATED_BY, PROCESSED_FLAG, ERROR_TEXT, WEIGHT, ZPROGRAM, PROCESSED)"
                        strInsert = strInsert & " VALUES("
                        strInsert = strInsert & SingleQuotes(strKey) & ","
                        strInsert = strInsert & SingleQuotes(g_str1274) & ","
                        strInsert = strInsert & SingleQuotes(lng37) & ","
                        strInsert = strInsert & SingleQuotes(g_strChargeFrt) & ","
                        strInsert = strInsert & SingleQuotes(g_strFreightCount) & ","
                        strInsert = strInsert & SingleQuotes(frmBOL.f1222.Text) & ","
                        strInsert = strInsert & SingleQuotes(g_strRef) & ","
                        strInsert = strInsert & Format(Date, "yyyymmdd") & ","
                        'Windows user
                        strInsert = strInsert & SingleQuotes(GetUserID())
                        strInsert = strInsert & ",'0','0',"
                        strInsert = strInsert & SingleQuotes(g_strTotalWeight) & ","
                        strInsert = strInsert & "'N'"
                        strInsert = strInsert & ",'0')"
                        'debugprintstrInsert
                        'MsgBox strInsert
                        'For i = 1 To g_strFreightCount
                            m_rsConnection_SAP.Execute strInsert
                        'Next
                End Select
                
'                strInsert = strInsert & "'',"
'                ' windows user
'                ' strInsert = strInsert & "'Cafeuser'"
'                strInsert = strInsert & SingleQuotes(GetUserID())
'                strInsert = strInsert & ",'','',"
'                strInsert = strInsert & SingleQuotes(g_strFreightWeight) & ","
'                strInsert = strInsert & "'N')"
            'MsgBox strInsert
            'strInsert = strInsert & SingleQuotes(FormatCharges(f35)) & ","
            'strInsert = strInsert & SingleQuotes(FormatCharges(f37)) & ","
            'strInsert = strInsert & SingleQuotes(str26(i)) & ","
            'strInsert = strInsert & SingleQuotes(f60) & ","
            'strInsert = strInsert & SingleQuotes(f116) & ","
            'strInsert = strInsert & "'Shipped')"
        End If
'        For i = 1 To g_strFreightCount
'            m_rsConnection.Execute strInsert
'
'        Next
    
    g_strImportYes = ""

Release:
    Call Kill(strDatabase)
    If rsMPS.State = adStateOpen Then rsMPS.Close
    Set rsMPS = Nothing
 End If
 Exit Sub

Handler:
    MsgBox Err.Description, , "modODBC.WriteBOLReply"
    Resume Release
    
    
End Sub
'---------------------------------------------------------------------------------------
' Procedure : WriteBatchEditReply()
' DateTime  : 1/17/2006
' Purpose   : REQUIRED SUB. Called after a successful Batch Error Edit transaction.
'             This is called for both regular and online return labels.
'---------------------------------------------------------------------------------------
'
Public Sub WriteBatchEditReply()
    
End Sub
' Procedure : WriteTagReply()
' DateTime  : 1/26/2006
' Purpose   : REQUIRED SUB. Called after a successful Call Tag/Express Tag transaction.
'---------------------------------------------------------------------------------------
'
Public Sub WriteTagReply()
    
End Sub
' Procedure : WriteCancelTagReply()
' DateTime  : 1/26/2006
' Purpose   : REQUIRED SUB. Called after a successful Cancel Call Tag/Express Tag transaction.
'---------------------------------------------------------------------------------------
'
Public Sub WriteCancelTagReply()
    
End Sub
'---------------------------------------------------------------------------------------
' Procedure : WriteReturnDelete()
' DateTime  : 7/14/2005
' Purpose   : REQUIRED SUB. Called after a successful return label delete transaction.
'             This is called for both regular and online return labels.
'---------------------------------------------------------------------------------------
'
Public Sub WriteReturnDelete(ByVal strTracking As String)
On Local Error GoTo Handler
    Dim strUpdate As String
    Dim strUserPwd As String    'DSN user and password seperated by ','
    Dim strUser As String       'DSN user name
    Dim strPwd As String        'DSN user password
    
    If Not Connect("PROD") Then Exit Sub
    
    strUpdate = "UPDATE itl_fedex_deliveries SET Processed_Flag = 'V'" & _
                " WHERE Waybill = '" & strTracking & "'"
    
    m_rsConnection.Execute strUpdate
    
Release:
   Call Kill("PROD")
   
   Exit Sub

Handler:
    MsgBox Err.Description, , "modODBC.WriteReturnDelete"
    Resume Release
    
End Sub
'---------------------------------------------------------------------------------------
' Procedure : WriteError()
' DateTime  : 7/5/2004
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             an FXRS Error reply transaction.
'---------------------------------------------------------------------------------------
'
Public Sub WriteError()
    MsgBox "Shipping Error: " & g_str3, vbCritical, "FSMS Error"
End Sub
'---------------------------------------------------------------------------------------
' Procedure : WriteDelete()
' DateTime  : 7/5/2004
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             successful FXRS Delete.
' Called    : frmShip g_clsParser_Parsecomplete() event on FSMS 123 reply
'---------------------------------------------------------------------------------------
'
Public Sub WriteDelete(ByVal strTracking As String)
On Local Error GoTo Handler
    Dim strUpdate As String
    Dim strUserPwd As String    'DSN user and password seperated by ','
    Dim strUser As String       'DSN user name
    Dim strPwd As String        'DSN user password
    
    If Not Connect("PROD") Then Exit Sub
    
    strUpdate = "UPDATE itl_fedex_deliveries SET Processed_Flag = 'V'" & _
                " WHERE Waybill = '" & strTracking & "'"
    
    m_rsConnection.Execute strUpdate
    
Release:
   Call Kill("PROD")

    Exit Sub
Handler:
    
    MsgBox Err.Description, , "modODBC.WriteDelete"
    Resume Release
    
End Sub
'---------------------------------------------------------------------------------------
' Procedure : AfterClose()
' DateTime  : 7/5/2004
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             FXRS Close.
'---------------------------------------------------------------------------------------
'
Public Sub AfterClose()
    
End Sub
'---------------------------------------------------------------------------------------
' Procedure : BeforeClose()
' DateTime  : 7/12/2006
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed before
'             FXRS Close.
'---------------------------------------------------------------------------------------
'
Public Function BeforeClose() As Boolean

    BeforeClose = True
    
End Function
'

'---------------------------------------------------------------------------------------
' Procedure : BeforeShip()
' DateTime  : 2/21/2005
' Purpose   : REQUIRED Function. Modified to Return Boolean which controls whether or not
'             the shipment will continue or be canceled. Return True to continue shipment,
'             False to cancel.
'---------------------------------------------------------------------------------------
'
Public Function BeforeShip() As Boolean
    
    BeforeShip = True

End Function
'

'---------------------------------------------------------------------------------------
' Procedure : BeforeShipTrans()
' DateTime  : 8/7/2008
' Purpose   : REQUIRED Function. Modified to Return Boolean which controls whether or not
'             the shipment will continue or be canceled. Return True to continue shipment,
'             False to cancel.
'---------------------------------------------------------------------------------------
'
Public Function BeforeShipTrans(ByRef TransString As String) As Boolean
    
    Dim lngMsgBoxResult As VbMsgBoxResult
    BeforeShipTrans = True
    
End Function
'

'---------------------------------------------------------------------------------------
' Procedure : Initialize()
' DateTime  : 7/5/2004
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed on
'             program execution.
'---------------------------------------------------------------------------------------
'
Public Sub Initialize(Optional f1 As Form)

End Sub
'

'---------------------------------------------------------------------------------------
' Procedure : BeforeBatch()
' DateTime  : 8/5/2004
' Purpose   : REQUIRED SUB. Called before an entire batch is processed.
'---------------------------------------------------------------------------------------
'
Public Function BeforeBatch() As Boolean

    BeforeBatch = True
    
End Function
'

'---------------------------------------------------------------------------------------
' Procedure : BeforeBatchShip()
' DateTime  : 8/5/2004
' Purpose   : REQUIRED SUB. Called just before a batch transaction is sent to the server.
'---------------------------------------------------------------------------------------
'
Public Function BeforeBatchShip() As Boolean

    BeforeBatchShip = True
    
End Function
'

'---------------------------------------------------------------------------------------
' Procedure : AfterBatchShip()
' DateTime  : 10/28/2005
' Purpose   : REQUIRED SUB. Called just after a batch transaction has been processed.
'---------------------------------------------------------------------------------------
'
Public Sub AfterBatchShip()

End Sub
'---------------------------------------------------------------------------------------
' Procedure : AfterBatch()
' DateTime  : 4/6/2005
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             a batch shipment has been completed.
'---------------------------------------------------------------------------------------
'
Public Sub AfterBatch()

End Sub
'---------------------------------------------------------------------------------------
' Procedure : AfterAddrLookup()
' DateTime  : 2/21/2005
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             cboCustID, cboContact, or cboCompany loses focus.
'---------------------------------------------------------------------------------------
'
Public Sub AfterAddrLookup()

End Sub
'---------------------------------------------------------------------------------------
' Procedure : AfterDeptLostFocus()
' DateTime  : 2/21/2005
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             f38 loses focus.
'---------------------------------------------------------------------------------------
'
Public Sub AfterDeptLostFocus()

End Sub
'---------------------------------------------------------------------------------------
' Procedure : AfterAddrVerifyUpdate()
' DateTime  : 9/13/2005
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             an address has been updated on the Ship form.
'---------------------------------------------------------------------------------------
'
Public Sub AfterAddrVerifyUpdate()

End Sub
'---------------------------------------------------------------------------------------
' Procedure : AfterLocationChange()
' DateTime  : 3/06/2006
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             the shipping Location has been changed on the Ship form.
'---------------------------------------------------------------------------------------
'
Public Sub AfterLocationChange()

End Sub
'---------------------------------------------------------------------------------------
' Procedure : AfterAddrVerify()
' DateTime  : 5/12/2006
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             Address Verification. Three possible status codes are passed. See comments
'             below.
'---------------------------------------------------------------------------------------
'
Public Sub AfterAddrVerify(ByVal intStatusCode As Integer)

    Select Case intStatusCode
        'Address is verfified
        Case 1
                    
        'Soft error
        Case 2
            
        'Hard error
        Case 3
            
    End Select

End Sub
'---------------------------------------------------------------------------------------
' Procedure : Connect()
' DateTime  : 7/5/2004
' Purpose   : Open connection to ODBC test DSN. Instantiate module level recordset.
'---------------------------------------------------------------------------------------
'
Private Function Connect(ByRef strDSNName As String) As Boolean
    On Local Error GoTo Handler
    
    
    Set m_rsRecords = New ADODB.Recordset
    
    '---------------------------------
    'Determine the database connection
    '---------------------------------
    Select Case strDSNName
        'Customer DSN
        Case "PROD"
             '-------------------------
              'Instantiate the recordset
             '-------------------------
             Set m_rsConnection = New ADODB.Connection
             
            '----------------------------------------
             'If the connection is not open then do so
             '----------------------------------------
             If m_rsConnection.State <> adStateOpen Then
             
                 m_rsConnection.Open m_strCUSTOMER_DSN, _
                     m_strDSN_USERNAME, _
                     m_strDSN_PASSWORD
                     
             End If
                
        Case "SAP"
            '-------------------------
            'Instantiate the recordset
            '-------------------------
            Set m_rsConnection_SAP = New ADODB.Connection
            
            '----------------------------------------
            'If the connection is not open then do so
            '----------------------------------------
            If m_rsConnection_SAP.State <> adStateOpen Then
            
                m_rsConnection_SAP.Open m_strCUSTOMER_DSN_SAP, _
                    m_strDSN_USERNAME_SAP, _
                    m_strDSN_PASSWORD_SAP
            
            End If
    End Select
    
    Connect = True
    
    Exit Function
        
Handler:
    MsgBox Err.Description, , "mododbc.connect"

End Function
'---------------------------------------------------------------------------------------------
' Procedure : Kill()
' DateTime  : 1/11/2006
' Purpose   : Close connection and recordset objects and set = to nothing
'---------------------------------------------------------------------------------------------
'
Public Sub Kill(strDB As String)
    On Local Error GoTo Handler
    
    Select Case strDB
        Case "PROD"
            If m_rsConnection.State = adStateOpen Then m_rsConnection.Close
            Set m_rsConnection = Nothing    'Kill Connection
        Case "SAP"
            If m_rsConnection_SAP.State = adStateOpen Then m_rsConnection_SAP.Close
            Set m_rsConnection_SAP = Nothing    'Kill Connection
    End Select
    
    If m_rsRecords.State = adStateOpen Then m_rsRecords.Close
    Set m_rsRecords = Nothing       'Kill Connection
    
    Exit Sub
    
Handler:
    MsgBox Err.Description, , "modODBC.Kill"

End Sub

'---------------------------------------------------------------------------------------
' Procedure : BuildCustomSPLabel()
' DateTime  : 20070620-04
' Purpose   : REQUIRED SUB. Can be used as hook to create custom EPL or ZPL buffer labels
'             for SmartPost.
'---------------------------------------------------------------------------------------
'
Public Function BuildCustomSPLabel(ByVal strLabel As String) As String

    BuildCustomSPLabel = strLabel

End Function
'


'---------------------------------------------------------------------------------------
' Procedure : AfterLostFocus()
' DateTime  : 2/21/2005
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             cboDept or f38 loses focus.
'---------------------------------------------------------------------------------------
'
Public Sub AfterLostFocus(c1 As Control)

    If c1.name = "f38" Then
        'g_clsReg.SetRegistryValue "Carry_f38", frmShip.f38, REG_SZ
    End If
    If c1.name = "f1273" Then
        'g_clsReg.SetRegistryValue "Carry_f1273", Left(frmShip.f1273.Text, 2), REG_SZ
    End If
    
End Sub
'

'---------------------------------------------------------------------------------------
' Procedure : AfterRateQuote()
' DateTime  : 20071106-01
' Purpose   : REQUIRED SUB. Can be used as hook to call routines to be executed after
'             a rate quote.
'---------------------------------------------------------------------------------------
'
Public Sub AfterRateQuote()

End Sub

Public Function ClearCommodity() As Boolean

End Function
'

Public Function ClearShipScreen() As Boolean

    ' m_strKeyValue = ""

End Function


#End If

